package ua.kiev.tosik.price.database.dao;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.log4j.Logger;
import ua.kiev.tosik.price.database.DbUtils;
import ua.kiev.tosik.price.database.connectors.ConnectorFactory;
import ua.kiev.tosik.price.database.connectors.IConnector;
import ua.kiev.tosik.price.models.Clients;
import ua.kiev.tosik.price.models.PricesFiles;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class PricesFilesDaoDerby implements IPricesFilesDao {

	private static Logger logger = Logger.getLogger("ua.kiev.tosik.price.database.dao");
	private Connection conn;
	private String colNames;
	/**
	 * Name of table in database
	 */
	private String tableName;

	public PricesFilesDaoDerby() {
		logger.debug("Initialize PricesFilesDaoDerby for Derby");
		tableName = "PRICES_FILES";
		colNames = "ID, CLIENTS_ID, FILE_NAME, FILE_SIZE, PRICES_DATE, CREATE_DATE, PARSED";
		try {
			IConnector connector = ConnectorFactory.getInstance("Derby");
			conn = connector.getConn();
		} catch (Exception e) {
			logger.fatal(e);
		}
	}

	@Override
	public List<PricesFiles> findAll() {
		List<PricesFiles> pricesFilesList = new ArrayList<PricesFiles>();
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT " + colNames + " FROM " + tableName + " ORDER BY PRICES_DATE DESC");
			while (rs.next()) {
				PricesFiles pricesFiles = new PricesFiles();
				pricesFiles.setId(rs.getInt("id"));
				pricesFiles.setClientsId(rs.getInt("clients_id"));
				pricesFiles.setFileName(rs.getString("file_name"));
				pricesFiles.setFileSize(rs.getInt("file_size"));
				pricesFiles.setPricesDate(rs.getDate("prices_date"));
				pricesFiles.setCreateDate(rs.getDate("create_date"));
				pricesFiles.setParsed((rs.getInt("parsed") != 0));
				pricesFilesList.add(pricesFiles);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return pricesFilesList;
	}

	@Override
	public PricesFiles findById(int id) {
		PricesFiles pricesFiles = null;
		try {
			PreparedStatement pst = conn.prepareStatement("SELECT " + colNames + " FROM " + tableName + " WHERE ID = ?");
			pst.setInt(1, id);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				pricesFiles = new PricesFiles();
				pricesFiles.setId(rs.getInt("id"));
				pricesFiles.setClientsId(rs.getInt("clients_id"));
				pricesFiles.setFileName(rs.getString("file_name"));
				pricesFiles.setFileSize(rs.getInt("file_size"));
				pricesFiles.setPricesDate(rs.getDate("prices_date"));
				pricesFiles.setCreateDate(rs.getDate("create_date"));
				pricesFiles.setParsed((rs.getInt("parsed") != 0));
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return pricesFiles;
	}

	@Override
	public List<PricesFiles> findByClientsId(int clientId) {
		logger.debug(this.getClass().getSimpleName() + ": findByClientsId()");
		List<PricesFiles> pricesFilesList = new ArrayList<PricesFiles>();
		try {
			PreparedStatement pst = conn.prepareStatement("SELECT " + colNames + " FROM " + tableName + " WHERE CLIENTS_ID = ? ORDER BY PRICES_DATE DESC");
			pst.setInt(1, clientId);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				PricesFiles pricesFiles = new PricesFiles();
				pricesFiles.setId(rs.getInt("id"));
				pricesFiles.setClientsId(rs.getInt("clients_id"));
				pricesFiles.setFileName(rs.getString("file_name"));
				pricesFiles.setFileSize(rs.getInt("file_size"));
				pricesFiles.setPricesDate(rs.getDate("prices_date"));
				pricesFiles.setCreateDate(rs.getDate("create_date"));
				pricesFiles.setParsed((rs.getInt("parsed") != 0));
				pricesFilesList.add(pricesFiles);
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		logger.debug(this.getClass().getSimpleName() + ": found: " + pricesFilesList.size());
		return pricesFilesList;
	}

	@Override
	public List<PricesFiles> findByClientsIdDate(int clientId, java.util.Date pricesDate) {
		List<PricesFiles> pricesFilesList = new ArrayList<PricesFiles>();
		try {
			PreparedStatement pst = conn.prepareStatement("SELECT " + colNames + " FROM " + tableName + " WHERE CLIENTS_ID = ? AND PRICES_DATE = ? ORDER BY PRICES_DATE DESC");
			pst.setInt(1, clientId);
			pst.setDate(2, getSqlDate(pricesDate));
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				PricesFiles pricesFiles = new PricesFiles();
				pricesFiles.setId(rs.getInt("id"));
				pricesFiles.setClientsId(rs.getInt("clients_id"));
				pricesFiles.setFileName(rs.getString("file_name"));
				pricesFiles.setFileSize(rs.getInt("file_size"));
				pricesFiles.setPricesDate(rs.getDate("prices_date"));
				pricesFiles.setCreateDate(rs.getDate("create_date"));
				pricesFiles.setParsed((rs.getInt("parsed") != 0));
				pricesFilesList.add(pricesFiles);
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return pricesFilesList;
	}

	public static java.sql.Date getSqlDate(String date) {
		java.sql.Date result = null;
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		try {
			result = new java.sql.Date(dateFormat.parse(date).getTime());
		} catch (ParseException e) {
			logger.error(e);
		}
		return result;
	}

	/**
	 * Create PricesFiles to database
	 * @param pricesFiles object for creation
	 * @return created PricesFiles from database
	 * @throws Exception if creation fail
	 */
	@Override
	public PricesFiles save(PricesFiles pricesFiles) throws Exception {
		byte[] tmpFile;
		if ( pricesFiles.getSourceFile() == null || pricesFiles.getSourceFile().length == 0 ) {
			logger.error("Can't save empty file");
			throw new Exception("Can't save zero file");
		} else {
			tmpFile = pricesFiles.getSourceFile();
		}
		logger.debug("Save PricesFiles");
		conn.setAutoCommit(false);
		try {
			PreparedStatement ps = conn.prepareStatement("INSERT INTO PRICES_FILES ( CLIENTS_ID, FILE_NAME, FILE_SIZE, SOURCE_FILE, PRICES_DATE) VALUES(?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1, pricesFiles.getClientsId());
			ps.setString(2, pricesFiles.getFileName());
			// BZip2CompressorInputStream bzIn = new BZip2CompressorInputStream(new ByteArrayInputStream(pricesFiles.getSourceFile()));
			ps.setInt(3, pricesFiles.getFileSize());
			ps.setBytes(4, pricesFiles.getSourceFile());
			ps.setDate(5, getSqlDate(pricesFiles.getPricesDate()));
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if ( rs.next() ) {
				pricesFiles = findById(rs.getInt(1));
			}
			rs.close();
			ps.close();
			conn.commit();
			if ( tmpFile != null && tmpFile.length > 0 ) {
				pricesFiles.setSourceFile(tmpFile);
			}
			return pricesFiles;
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
	}

	@Override
	public PricesFiles update(PricesFiles pricesFiles) throws Exception {
		logger.debug("Try to update PricesFiles record");
		PricesFiles pricesFilesInDb = findById(pricesFiles.getId());
		String updateSql = generateUpdateSql(pricesFilesInDb, pricesFiles);
		if ( updateSql.equals("") ) {
			return pricesFilesInDb;
		}
		conn.setAutoCommit(false);
		try {
			String sqlQuery = "UPDATE " + tableName + " SET " + updateSql + " WHERE ID = " + pricesFiles.getId();
			// logger.debug("SQL: " + sqlQuery);
			Statement st = conn.createStatement();
			st.executeUpdate(sqlQuery);
			st.close();
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
		return findById(pricesFiles.getId());
	}

	@Override
	public void delete(PricesFiles pricesFiles) throws Exception {
		logger.debug("Delete PricesFiles");
		conn.setAutoCommit(false);
		try {
			PreparedStatement ps = conn.prepareStatement("DELETE FROM PRICES_FILES WHERE ID = ?");
			ps.setInt(1, pricesFiles.getId());
			ps.executeUpdate();
			ps.close();
			conn.commit();
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
	}

	@Override
	public void deleteAll(Clients clients) throws Exception {
		logger.debug("Delete All PricesFiles in client " + clients.getName());
		conn.setAutoCommit(false);
		try {
			PreparedStatement ps = conn.prepareStatement("DELETE FROM PRICES_FILES WHERE CLIENTS_ID = ?");
			ps.setInt(1, clients.getId());
			ps.executeUpdate();
			ps.close();
			conn.commit();
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
	}

	@Override
	public void deleteAll() throws Exception {
		logger.debug("Delete All PricesFiles in all clients ");
		conn.setAutoCommit(false);
		try {
			PreparedStatement ps = conn.prepareStatement("DELETE FROM PRICES_FILES");
			ps.executeUpdate();
			ps.close();
			conn.commit();
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
	}

	@Override
	public byte[] loadFile(int fileId) throws Exception {
		logger.debug("Load PricesFiles file");
		byte[] fileContent = new byte[0];
		conn.setAutoCommit(false);
		try {
			PreparedStatement ps = conn.prepareStatement("SELECT SOURCE_FILE FROM PRICES_FILES WHERE ID = ?");
			ps.setInt(1, fileId);
			ResultSet rs = ps.executeQuery();
			if ( rs.next() ) {
				fileContent = rs.getBytes(1);
			}
			rs.close();
			ps.close();
			conn.commit();
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
		return fileContent;
	}

	public static java.sql.Date getSqlDate(java.util.Date date) {
		return new java.sql.Date(date.getTime());
	}

	private String generateUpdateSql(PricesFiles pricesFilesInDb, PricesFiles pricesFiles) {
		HashMap<String, String> SQL = new HashMap<String, String>();
		if ( !DbUtils.stringEquals(pricesFilesInDb.getFileName(), pricesFiles.getFileName()) ) {
			// SQL.put("FILE_NAME", "'" + DbUtils.encodeForSql(pricesFiles.getFileName()) + "'");
			SQL.put("FILE_NAME", "'" + StringEscapeUtils.escapeSql(pricesFiles.getFileName()) + "'");
		}
		if ( pricesFilesInDb.isParsed() != pricesFiles.isParsed() ) {
			SQL.put("PARSED", ((pricesFiles.isParsed())?"1":"0"));
		}

		StringBuilder str = new StringBuilder();
		Iterator it = SQL.entrySet().iterator();
		while ( it.hasNext() ) {
			Map.Entry pairs = (Map.Entry)it.next();
			str.append(pairs.getKey()).append(" = ").append(pairs.getValue());
			if ( it.hasNext() ) {
				str.append(", ");
			}
		}
		return str.toString();
	}

}
